package org.lq.system.dao.impl;

import lombok.extern.log4j.Log4j;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.lq.system.dao.StaffInfoDao;
import org.lq.system.entity.StaffInfo;
import org.lq.util.JDBCUtil;

import java.sql.SQLException;
import java.util.List;

/**
 * @author 马秋阳
 * @create 2020-10-13 18:57
 */
@Log4j
public class StaffInfoDaoImpl  implements StaffInfoDao {
    /**
     * t添加
     *
     * @param staffInfo
     * @return
     */
    @Override
    public int save(StaffInfo staffInfo) {
        log.info("进入数据访问层 -->[StaffInfoDaoImpl]下的save方法");
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        int num = 0;
        try {
            num = runner.update("insert into staff_info (" +
                            "role_id, " +
                            "staff_name, " +
                            "staff_sex, " +
                            "staff_age, " +
                            "staff_native_place, " +
                            "staff_idcard, " +
                            "staff_brithday, " +
                            "staff_office_phone, " +
                            "staff_mobile_phone, " +
                            "staff_eamil, " +
                            "staff_addr, " +
                            "staff_qq, " +
                            "staff_entry_time, " +
                            "staff_eduction_level, " +
                            "staff_remark, " +
                            "staff_state, " +
                            "user_number, " +
                            "user_passowrd) " +
                            "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)",
                    staffInfo.getRoleId(),
                    staffInfo.getStaffName(),
                    staffInfo.getStaffSex(),
                    staffInfo.getStaffAge(),
                    staffInfo.getStaffNativePlace(),
                    staffInfo.getStaffIdcard(),
                    staffInfo.getStaffBrithday(),
                    staffInfo.getStaffOfficePhone(),
                    staffInfo.getStaffMobilePhone(),
                    staffInfo.getStaffEamil(),
                    staffInfo.getStaffAddr(),
                    staffInfo.getStaffQq(),
                    staffInfo.getStaffEntryTime(),
                    staffInfo.getStaffEductionLevel(),
                    staffInfo.getStaffRemark(),
                    staffInfo.getStaffState(),
                    staffInfo.getUserNumber(),
                    staffInfo.getUserPassowrd());
        } catch (SQLException e) {
            log.error("添加异常失败",e);
        }
        log.info("添加成功,返回结果"+num);
        log.info("离开数据访问层 -->[StaffInfoDaoImpl] 下的save方法");
        return num;
    }

    /**
     * 修改
     *
     * @param staffInfo
     * @return
     */
    @Override
    public int update(StaffInfo staffInfo) {
        log.info("进入数据访问层 -->[StaffInfoDaoImpl]下的update方法");
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        int num = 0;
        try {
            num = runner.update("update staff_info set(" +
                            "role_id=?, " +
                            "staff_name=?, " +
                            "staff_sex=?, " +
                            "staff_age=?, " +
                            "staff_native_place=?, " +
                            "staff_idcard=?, " +
                            "staff_brithday=?, " +
                            "staff_office_phone=?, " +
                            "staff_mobile_phone=?, " +
                            "staff_eamil=?, " +
                            "staff_addr=?, " +
                            "staff_qq=?, " +
                            "staff_entry_time=?, " +
                            "staff_eduction_level=?, " +
                            "staff_remark=?, " +
                            "staff_state=?, " +
                            "user_number=?, " +
                            "user_passowrd=? where staff_id = ?)",
                    staffInfo.getRoleId(),
                    staffInfo.getStaffName(),
                    staffInfo.getStaffSex(),
                    staffInfo.getStaffAge(),
                    staffInfo.getStaffNativePlace(),
                    staffInfo.getStaffIdcard(),
                    staffInfo.getStaffBrithday(),
                    staffInfo.getStaffOfficePhone(),
                    staffInfo.getStaffMobilePhone(),
                    staffInfo.getStaffEamil(),
                    staffInfo.getStaffAddr(),
                    staffInfo.getStaffQq(),
                    staffInfo.getStaffEntryTime(),
                    staffInfo.getStaffEductionLevel(),
                    staffInfo.getStaffRemark(),
                    staffInfo.getStaffState(),
                    staffInfo.getUserNumber(),
                    staffInfo.getUserPassowrd(),
                    staffInfo.getStaffId());
        } catch (SQLException e) {
            log.error("修改异常失败"+e);
        }
        log.info("修改成功,返回结果"+num);
        log.info("离开数据访问层 -->[StaffInfoDaoImpl]下的update方法");
        return num;
    }

    /**
     * 删除
     *
     * @param id
     * @return
     */
    @Override
    public int delete(int id) {
        log.info("进入数据访问层 -->[StaffInfoDaoImpl]下的delete方法");
        int num = 0;
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            num = runner.update("delete from staff_info where staff_id = ?",id);
        } catch (SQLException e) {
            log.error("删除异常失败"+e);
        }
        log.info("删除成功,返回结果"+num);
        log.info("离开数据访问层 -->[StaffInfoDaoImpl]下的delete方法");
        return num;
    }

    /**
     * 通过ID查询
     *
     * @param id
     * @return
     */
    @Override
    public StaffInfo getById(int id) {
        log.info("进入数据访问层 -->[StaffInfoDaoImpl]下的getById方法");
        StaffInfo info = null;
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            info = runner.query("select * from staffinfo where staffId = ?",
                    new BeanHandler<>(StaffInfo.class),
                    id);
        } catch (SQLException e) {
            log.error("通过byId查询异常失败"+e);
        }
        log.info("根据byId查询成功,返回结果"+info);
        log.info("离开数据访问层 -->[StaffInfoDaoImpl]下的getById方法");
        return info;
    }

    /**
     * 总行数
     *
     * @return
     */
    @Override
    public int getCount() {
        log.info("进入数据访问层 -->[StaffInfoDaoImpl]下的getCount方法");
        int count = 0;
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            Long num = runner.query("select count(1) from staffinfo;", new ScalarHandler<Long>());
            count = num.intValue();
        } catch (SQLException e) {
            log.error("总行数查询获取异常失败"+e);
        }
        log.info("查询总行数成功,返回结果"+count);
        log.info("离开数据访问层 -->[StaffInfoDaoImpl]下的getCount方法");
        return count;
    }

    /**
     * 分页查询
     *
     * @param startIndex
     * @param pageSize
     * @return
     */
    @Override
    public List<StaffInfo> pageList(int startIndex, int pageSize) {
        log.info("进入数据访问层 -->[StaffInfoDaoImpl]下的pageList方法");
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        List<StaffInfo> all = null;
        try {
            all = runner.query("select * from staffinfo limit ?,?",
                    new BeanListHandler<StaffInfo>(StaffInfo.class),
                    startIndex,pageSize);
        } catch (SQLException e) {
            log.error("分页查询异常失败"+e);
        }
        log.info("分页查询成功,返回结果"+all);
        log.info("离开数据访问层 -->[StaffInfoDaoImpl]下的pageList方法");
        return all;
    }

    /**
     * 根据条件查询总行数
     *
     * @param values
     * @return
     */
    @Override
    public int getCount(String... values) {
        log.info("进入数据访问层 -->[StaffInfoDaoImpl]下的getCount(String... values)方法");
        int count = 0;
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            Long num = runner.query("select count(1) from staffinfo where staffName like ?",
            new ScalarHandler<Long>(),
            "%"+values+"%");
            count = num.intValue();
        } catch (SQLException e) {
            log.error("根据条件获取总行数异常失败"+e);
        }
        log.info("根据条件获取总行数成功,返回结果"+count);
        log.info("离开数据访问层 -->[StaffInfoDaoImpl]下的getCount(String... values)方法");
        return count;
    }

    /**
     * 根据条件分页查询
     *
     * @param startIndex
     * @param pageSize
     * @param value
     * @return
     */
    @Override
    public List<StaffInfo> pageByValues(int startIndex, int pageSize, String... value) {
        log.info("进入数据访问层 -->[StaffInfoDaoImpl]下的pageByValues(,,String... values)方法");
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        List<StaffInfo> all = null;
        try {
            all = runner.query("select * from staffinfo where staffName like ? limit ?,?",
                    new BeanListHandler<StaffInfo>(StaffInfo.class),
                    "%"+value+"%",startIndex,pageSize);
        } catch (SQLException e) {
            log.error("根据条件分页查询异常失败"+e);
        }
        log.info("根据条件分页查询成功,返回结果"+all);
        log.info("离开数据访问层 -->[StaffInfoDaoImpl]下的pageByValues(,,String... values)方法");
        return all;
    }

    /**
     * 根据角色名称查询
     *
     * @param roleId
     * @return
     */
    @Override
    public List<StaffInfo> getByRoleId(int roleId) {
        log.info("进入数据访问层 -->[StaffInfoDaoImpl]下的getByRoleId(int roleId)方法");
        List<StaffInfo> getByRoleId = null;
        QueryRunner runner = new QueryRunner(JDBCUtil.getDataSource());
        try {
            getByRoleId = runner.query("select * from staffinfo where roleId = ?",
                    new BeanListHandler<StaffInfo>(StaffInfo.class),
                    roleId);
        } catch (SQLException e) {
            log.error("根据角色名称查询异常失败"+e);
        }
        log.info("根据角色名称查询成功,返回结果"+getByRoleId);
        log.info("离开数据访问层 -->[StaffInfoDaoImpl]下的getByRoleId(int roleId)方法");
        return getByRoleId;
    }
}
